﻿using DataAccessService.IService;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using DataAccessService.Repository;
using System.Data;

namespace DataAccessService.Service
{
    public class SqliteService : BaseRepository,ISqliteService
    {
        private SqliteExcuteRepository sqliteExcuteRepository = new SqliteExcuteRepository();

        public SQLiteConnection GetConn()
        {
            SqliteConnectRepository repository = new SqliteConnectRepository();
            SQLiteConnection conn = repository.GetConn();

            return conn;
        }

        public List<T> GetList<T>(string whereClause)
        {
            SQLiteConnection conn = GetConn();
            string sql = string.Format("select * from {0} {1}", typeof(T).Name, string.IsNullOrEmpty(whereClause) ? "" : ("where "+ whereClause));

            List<T> items = sqliteExcuteRepository.ExcuteQuerySQL<T>(conn, sql);

            if (null != conn)
                conn.Close();

            return items;
        }

        public bool AddItem<T>(T item, string idField)
        {
            SQLiteConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = GetInsertSql<T>(item, idField, columns);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, sql);

            conn.Close();

            return result;
        }

        public bool UpdateItem<T>(T item, string idField, string id)
        {
            SQLiteConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = GetUpdateSql<T>(item, idField, id,columns);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool DeleteItem(string tableName, string idField, string id)
        {
            SQLiteConnection conn = GetConn();

            string sql = string.Format("delete from {0} where {1}='{2}'", tableName, idField, id);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool ExcuteSql(SQLiteConnection conn, string sql)
        {
            bool result= sqliteExcuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool AddItem<T>(SQLiteConnection conn, SQLiteTransaction tran, T item, string idField)
        {
            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = GetInsertSql<T>(item, idField,columns);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }

        public bool DeleteItem(SQLiteConnection conn, SQLiteTransaction tran, string tableName, string idField, string id)
        {
            string sql = string.Format("delete from {0} where {1}={2}", tableName, idField, id);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }


        public bool UpdateItem<T>(SQLiteConnection conn, SQLiteTransaction tran, T item, string idField, string id)
        {
            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = GetUpdateSql<T>(item, idField, id,columns);
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }

        /// <summary>
        /// 获取表格字段集合
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<string> GetColumnNames(SQLiteConnection conn, string tableName)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);
            DataSet dataSet = sqliteExcuteRepository.ExcuteQuerySQL(conn, sql);

            List<string> columnsList = new List<string>();
            foreach (DataColumn column in dataSet.Tables[0].Columns)
            {
                columnsList.Add(column.ColumnName);
            }
            return columnsList;
        }

        public List<T> QueryList<T>(string sql)
        {
            SQLiteConnection conn = GetConn();
            List<T> items = sqliteExcuteRepository.ExcuteQuerySQL<T>(conn, sql);

            if (null != conn)
                conn.Close();

            return items;
        }

        public bool ExcuteSql(SQLiteConnection conn, SQLiteTransaction tran, string sql)
        {
            bool result = sqliteExcuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }
    }
}

